CREATE DATABASE Recruitment
GO

USE Recruitment
GO

CREATE TABLE Department
(
	DepartmentID VARCHAR(5) PRIMARY KEY NOT NULL,
	DepartmentName VARCHAR(30) NOT NULL
)
GO

INSERT INTO Department VALUES('D0001','HRD')
INSERT INTO Department VALUES('D0002','ITV')
INSERT INTO Department VALUES('D0003','Finance-Banking')
INSERT INTO Department VALUES('D0004','Software')
INSERT INTO Department VALUES('D0005','Sales')
INSERT INTO Department VALUES('D0006','Export-Import')
GO

CREATE TABLE Employee
(
	EmpID VARCHAR(30) PRIMARY KEY NOT NULL,
	DepartmentID VARCHAR(5) FOREIGN KEY REFERENCES Department(DepartmentID) NOT NULL,
	UserName VARCHAR(50), 
	Password VARCHAR(30) NOT NULL,
	FullName VARCHAR(50),
	Address VARCHAR(50),
	Phone VARCHAR(15),
	Email VARCHAR(30)
)
GO

INSERT INTO Employee VALUES('E0001','D0001','maria','123456','Hoang Thuy Linh','Hai Phong','01677239979','babyhachi@gmail.com')
INSERT INTO Employee VALUES('E0002','D0002','Kevin','123456','Tran Bon','Ha Noi','01677239979','babyhachi@gmail.com')
INSERT INTO Employee VALUES('E0003','D0003','Martin','123456','Ly Nho','PT','01677239979','babyhachi@gmail.com')
INSERT INTO Employee VALUES('E0004','D0004','Paul','123456','Vu Phong','HN','01677239979','babyhachi@gmail.com')
INSERT INTO Employee VALUES('E0005','D0005','Robin','123456','Le Hoang','HN','01677239979','babyhachi@gmail.com')
INSERT INTO Employee VALUES('E0006','D0006','fire','123456','Le Van','HN','01677239979','babyhachi@gmail.com')
GO

CREATE TABLE Vacancy
(
	VacancyID VARCHAR(5) PRIMARY KEY NOT NULL,
	DepartmentID VARCHAR(5) FOREIGN KEY REFERENCES Department(DepartmentID) NOT NULL, 
	VacancyTitle VARCHAR(100) NOT NULL,
	NumOfJob INT NOT NULL,
	Salary VARCHAR(20) NOT NULL,
	Experience VARCHAR(20) NOT NULL,
	StartDate DATETIME DEFAULT GETDATE(), -- Default as this day.
	CloseDate DATETIME,
	Status VARCHAR(10) NOT NULL, -- Open/Close/Suspended.
	Place varchar(50)
)

GO
INSERT INTO Vacancy VALUES('V0001','D0004','Android Developer',1,'250 - 300 $','1 years',
DEFAULT,'5/5/2012','Open', 'Ha noi')
INSERT INTO Vacancy VALUES('V0002','D0004','PHP Developer',3,'500 - 700 $','1 years',
DEFAULT,'5/6/2012','Open','Hai phong')
INSERT INTO Vacancy VALUES('V0003','D0004','Java Leader.',2,'500 - 700 $','1 years',
DEFAULT,'5/7/2012','Open','Sai gon')
INSERT INTO Vacancy VALUES('V0004','D0004','Graphic Designer ',4,'500 - 700 $','1 years',
DEFAULT,'5/8/2012','Open','Ha noi')
INSERT INTO Vacancy VALUES('V0005','D0004','Iphone Engineer',5,'500 - 700 $','1 years',
DEFAULT,'5/9/2012','Open','Ha noi')
INSERT INTO Vacancy VALUES('V0006','D0004','.NET Engineer ',1,'500 - 700 $','1 years',
DEFAULT,'5/10/2012','Suspended','Sai gon')

INSERT INTO Vacancy VALUES('V0007','D0005', 'Sales Manager',2,'250 - 300 $','2 years',
DEFAULT,'4/10/2012','Open','Hai phong')
INSERT INTO Vacancy VALUES('V0008','D0005','Sales Director',3,'500 - 700 $','2 years',
DEFAULT,'4/11/2012','Open','Ha noi')
INSERT INTO Vacancy VALUES('V0009','D0005','Sales Support',4,'500 - 700 $','2 years',
DEFAULT,'4/12/2012','Open','Hai phong')
INSERT INTO Vacancy VALUES('V0010','D0005','Technical Sales',5,'500 - 700 $','2 years',
DEFAULT,'4/13/2012','Open','Sai gon')
INSERT INTO Vacancy VALUES('V0015','D0005','Sales Consultant',1,'500 - 700 $','2 years',
DEFAULT,'4/14/2012','Open','Ha noi')
INSERT INTO Vacancy VALUES('V0016','D0005','Sales Excutive',2,'500 - 700 $','2 years',
DEFAULT,'4/15/2012','Suspended','Hai phong')

INSERT INTO Vacancy VALUES('V0011','D0003','Deputy Director',3,'250 - 300 $','3 years',
DEFAULT,'5/7/2012','Open','Sai gon')
INSERT INTO Vacancy VALUES('V0012','D0003','Director Business',4,'500 - 700 $','3 years',
DEFAULT,'5/25/2012','Open','Sai gon')
INSERT INTO Vacancy VALUES('V0013','D0003','Marketing Dept',5,'500 - 700 $','3 years',
DEFAULT,'5/15/2012','Suspended','Hai phong')
INSERT INTO Vacancy VALUES('V0014','D0003','Pricing Controller',1,'500 - 700 $','3 years',
DEFAULT,'4/30/2012','Open','Ha noi')

INSERT INTO Vacancy VALUES('V0018','D0006','Import Staff',2,'250 - 300 $','4 years',
DEFAULT,'4/22/2012','Open','Ha noi')
INSERT INTO Vacancy VALUES('V0019','D0006','Export Staff',3,'500 - 700 $','4 years',
DEFAULT,'4/23/2012','Open','Ha noi')
INSERT INTO Vacancy VALUES('V0020','D0006','Sale Logistics',4,'500 - 700 $','4 years',
DEFAULT,'4/24/2012','Open','Hai phong')
INSERT INTO Vacancy VALUES('V0021','D0006','Textile Merchandiser',5,'500 - 700 $','4 years',
DEFAULT,'4/25/2012','Open','Sai gon')
GO

CREATE TABLE Applicant
(
	ApplicantID VARCHAR(5) PRIMARY KEY NOT NULL,
	Username VARCHAR(50) NOT NULL,
	Password VARCHAR(50) NOT NULL,	
	ApplicantName VARCHAR(50) NOT NULL,
	AppPhone VARCHAR(20) NOT NULL,
	AppEmail VARCHAR(30) NOT NULL,
	AppAbout TEXT NOT NULL,
	DateCreate DATETIME DEFAULT GETDATE(), -- Date created default by this day.	
	Status VARCHAR(50) -- NotInProcess/InProcess/Hired/Banned.
)
GO

INSERT INTO Applicant VALUES ('A0001','morris', '123456','Nguyen van minh','123456789','baduy2291@gmail.com','minh','11/01/2011','Not Inprocess' )
INSERT INTO Applicant VALUES ('A0002','saigon', '456123','Nguyen van chien','12533434','baduy2291@gmail.com','chien','10/01/2011','Not Inprocess' )
INSERT INTO Applicant VALUES ('A0003','tokyo', '789456','Nguyen van tien','343545654','baduy2291@gmail.com','tien','08/11/2011','Not Inprocess' )
INSERT INTO Applicant VALUES ('A0004','seoul', 'korea','Dao van minh','786524323','baduy2291@gmail.com','minh','11/01/2011','Not Inprocess' )
INSERT INTO Applicant VALUES ('A0005','london', 'england','Nguyen minh son','123456789','baduy2291@gmail.com','son','11/01/2011','Inprocess' )
INSERT INTO Applicant VALUES ('A0006','newyork', 'united states','Tran van tien','453456672','baduy2291@gmail.com','tien','10/11/2011','Inprocess' )
INSERT INTO Applicant VALUES ('A0007','sydney', 'australia','Do thi nga','12342235','baduy2291@gmail.com','minh','01/11/2011','Inprocess' )
INSERT INTO Applicant VALUES ('A0008','boston', 'celtic','Nguyen van ba','56553323','baduy2291@gmail.com','minh','05/14/2011','Hired' )
INSERT INTO Applicant VALUES ('A0009','laker', 'galaxy','Le thi huong','93334323','baduy2291@gmail.com','minh','07/05/2011','Banned' )
GO

CREATE TABLE Interview
(
	VacancyID VARCHAR(5) FOREIGN KEY REFERENCES Vacancy(VacancyID) NOT NULL,
	ApplicantID VARCHAR(5) FOREIGN KEY REFERENCES Applicant(ApplicantID) NOT NULL,
	EmpID VARCHAR(30) FOREIGN KEY REFERENCES Employee(EmpID),	
	ScheduleDate DATETIME,
	AttachedDate DATETIME,
	StartTime VARCHAR(20),
	EndTime VARCHAR(20),
	Command NVARCHAR(30),
	PRIMARY KEY(VacancyID,ApplicantID),
	Status VARCHAR(50) -- Scheduled/Selected/Rejected/Not Required
)
GO

INSERT INTO Interview VALUES ('V0002','A0002','E0004','10/22/2012','10/30/2012','2h','3h','good','Selected')
INSERT INTO Interview VALUES ('V0002','A0003','E0004','10/22/2012','10/30/2012','2h','3h','good','Selected')
GO

----------------------+    Employee table   +----------------------------------
--> Check Accounts.
CREATE PROC checkLoginEmp
(
	@username VARCHAR(50),
	@password VARCHAR(30)
)
AS
SELECT * FROM Employee WHERE UserName = @username AND Password = @password
go
-------------------------------
--> Get all employees
CREATE PROC getAllEmployee
AS
SELECT * FROM Employee
go

--> Filter Employee by Username
CREATE PROC sp_getEmpByUsername
@username VARCHAR(100)
AS
SELECT * FROM Employee WHERE Username LIKE '%' + @username + '%'
GO

--> Filter Employee by Email
CREATE PROC sp_getEmpByEmail
@email VARCHAR(100)
AS
SELECT * FROM Employee WHERE Email LIKE '%' + @email + '%'
GO
------------------------------------
create proc insertEmployee
(
	@empid varchar(30),
	@departmentid varchar(5),
	@username varchar(50),
	@password varchar(30),
	@fullname varchar(50),
	@address varchar(50),
	@phone varchar(15),
	@email varchar(30)
)
as insert into Employee values(@empid, @departmentid, @username, @password, @fullname, @address, @phone, @email)
go
------------------------------------------------------------------------------------
create proc getOneEmployee
(@empid varchar(30))
as select * from Employee where EmpID = @empid
go
---------------------------------------------------------------------------------------
create proc updateEmployee
(
	@empid varchar(30),
	@departmentid varchar(5),
	@username varchar(50),
	@password varchar(30),
	@fullname varchar(50),
	@address varchar(50),
	@phone varchar(15),
	@email varchar(30)
)
as update Employee set DepartmentId=@departmentid, Username=@username, 
	Password=@password, FullName=@fullname, Address=@address, Phone=@phone, Email=@email
	where EmpID=@empid
go
-------------------------------------------------------------------------------------
create proc deleteEmployee
(
	@empid varchar(30)
)
as delete from Employee where EmpID = @empid
go
-----------------------------------------------------------------------------



-----------------------+    Vacancy table    +-------------------------------
--> Get all infomations of vacancy.
CREATE PROC sp_getAllVacancy
AS
SELECT * FROM Vacancy
GO

--> Filter vacancy by VacancyTitle
CREATE PROC sp_getVacancyByTitle
@vacancyTitle VARCHAR(100)
AS
SELECT * FROM Vacancy WHERE VacancyTitle LIKE '%' + @vacancyTitle + '%'
GO

--> Filter vacancy by VacancyTitle for App.
CREATE PROC sp_getVacancyByTitleForApp
@vacancyTitle VARCHAR(100)
AS
SELECT * FROM Vacancy WHERE VacancyTitle LIKE @vacancyTitle AND Status = 'Open'
GO

--> Filter vacancy by Salary.
CREATE PROC sp_getVacancyBySalary
@salary VARCHAR(100)
AS
SELECT * FROM Vacancy WHERE Salary = @salary
GO

--> Filter vacancy by Salary for App.
CREATE PROC sp_getVacancyBySalaryForApp
@salary VARCHAR(100)
AS
SELECT * FROM Vacancy WHERE Salary = @salary AND Status = 'Open'
GO

--> Filter vacancy by Experience
CREATE PROC sp_getExperience
@experience VARCHAR(100)
AS
SELECT * FROM Vacancy WHERE Experience = @experience 
GO

--> Insert a record to data.
CREATE PROC sp_addNewVacancy
@vacancyID VARCHAR(5),
@departmentID VARCHAR(5), 
@vacancyTitle VARCHAR(100),
@numOfJob INT,
@salary VARCHAR(20),
@experience VARCHAR(20),
@startDate DATETIME, -- Default as this day.
@closeDate DATETIME,
@status VARCHAR(10), -- Open/Close/Suspended.
@place varchar(50)
AS 
INSERT INTO Vacancy(VacancyID,DepartmentID,VacancyTitle,NumOfJob,Salary,Experience,StartDate,CloseDate,Status, Place)  Values (@vacancyID,@departmentID,@vacancyTitle,@numOfJob,@salary,@experience,@startDate,@closeDate,@status, @place)
GO

--> Update info of table vacancy.
CREATE PROC sp_updateVacancy
@vacancyID VARCHAR(5),
@departmentID VARCHAR(5),
@vacancyTitle VARCHAR(100),
@numOfJob INT,
@salary VARCHAR(20),
@experience VARCHAR(20),
@startDate DATETIME, -- Default as this day.
@closeDate DATETIME,
@status VARCHAR(10), -- Open/Close/Suspended.
@place varchar(50)
AS
UPDATE Vacancy
SET VacancyTitle = @vacancyTitle, DepartmentID = @departmentID, NumOfJob = @numOfJob, Salary = @salary, @Experience = @experience, StartDate = @startDate, CloseDate = @closeDate, Status = @status, Place = @place
WHERE VacancyID = @vacancyID
GO

--> Delete info of table vacancy.
CREATE PROC sp_deleteVacancy
@vacancyID NVARCHAR(5)
AS
DELETE FROM Vacancy WHERE VacancyID = @vacancyID
GO

CREATE PROC getOneVacancy
@vacancyID VARCHAR(5)
AS SELECT * FROM Vacancy WHERE VacancyID = @vacancyID
GO

CREATE PROC sp_getDPtoUp
AS SELECT distinct DepartmentID FROM Department 
GO

-------------------------------------------------------------------------------
-----------------------+    Applicant table    +-------------------------------
--> Get all infomations of applicant.
CREATE PROC sp_getAllApplicant
AS
SELECT * FROM Applicant
GO

--> Filter applicant by username
CREATE PROC sp_getApplicantByUsername
@username VARCHAR(50)
AS
SELECT * FROM Applicant WHERE Username LIKE  '%' + @username + '%' 
GO

--> Get one Applicant to update.
CREATE PROC getOneApplicant
@applicantID VARCHAR(5)
AS SELECT * FROM Applicant WHERE ApplicantID = @applicantID
GO

--> Filter applicant by Email.
CREATE PROC sp_getApplicantByEmail
@Email VARCHAR(100)
AS
SELECT * FROM Applicant WHERE AppEmail LIKE '%' + @Email + '%' 
GO


--> Insert a record to data.
CREATE PROC sp_addNewApplicant
@applicantID VARCHAR(5),
@username VARCHAR(50), 
@password VARCHAR(50),
@applicantName VARCHAR(50),
@applicantPhone VARCHAR(20),
@applicantEmail VARCHAR(30),
@applicantAbout TEXT, 
@dateCreate DATETIME,
@status VARCHAR(50)
AS 
INSERT INTO Applicant(ApplicantID,Username,Password,ApplicantName,appPhone,AppEmail,AppAbout,DateCreate,Status)  Values (@applicantID,@username,@password,@applicantName,@applicantPhone,@applicantEmail,@applicantAbout,@dateCreate,@status)
GO

--> Update info of table vacancy.
CREATE PROC sp_updateApplicant
@applicantID VARCHAR(5),
@username VARCHAR(50), 
@password VARCHAR(50),
@applicantName VARCHAR(50),
@applicantPhone VARCHAR(20),
@applicantEmail VARCHAR(30),
@applicantAbout TEXT, 
@dateCreate DATETIME,
@status VARCHAR(50)
AS
UPDATE Applicant
SET Username = @username, Password = @password, ApplicantName = @applicantName, AppPhone = @applicantPhone, AppEmail = @applicantEmail, AppAbout = @applicantAbout, DateCreate = @dateCreate, Status = @status 
WHERE ApplicantID = @applicantID
GO

--> Delete info of table applicant.
CREATE PROC sp_deleteApplicant
@applicantID NVARCHAR(5)
AS
DELETE FROM Applicant WHERE ApplicantID = @applicantID
GO

--> List of applicant in status not in process.
CREATE PROC sp_notInProcess
AS
SELECT * FROM Applicant WHERE Status = 'Not InProcess'
GO


CREATE PROC sp_countProcess
AS
SELECT COUNT(*) AS Process FROM Applicant WHERE Status = 'Not InProcess'
GO

CREATE PROC sp_viewApp
@status NVARCHAR(100)
AS
SELECT * FROM Applicant WHERE Status = @status

---------------------------------------------------------------------------

select v.VacancyTitle, v.Salary, v.CloseDate, i.Status
from Vacancy v inner join Interview i
on v.VacancyID = i.VacancyID
where i.ApplicantID = 'A0009'

select v.VacancyID, v.VacancyTitle, a.ApplicantName, a.ApplicantID, i.AttachedDate, i.Status
from Vacancy v inner join Interview i
on v.VacancyID = i.VacancyID
inner join Applicant a
on a.ApplicantID = i.ApplicantID
where i.Status = 'Selected'
order by i.AttachedDate desc

select v.VacancyTitle, a.ApplicantName, i.*
from Vacancy v inner join Interview i
on v.VacancyID = i.VacancyID
inner join Applicant a
on a.ApplicantID = i.ApplicantID
where i.VacancyID = 'V0006' and i.ApplicantID = 'A0009'

select * from Vacancy where DepartmentID != 'D0001' and DepartmentID != 'D0002'

select distinct Experience from Vacancy 

select v.VacancyID, a.ApplicantID, v.VacancyTitle, a.ApplicantName, i.AttachedDate, i.Status, v.DepartmentID 
                    from Vacancy v inner join Interview i
                    on v.VacancyID = i.VacancyID
                    inner join Applicant a
                    on a.ApplicantID = i.ApplicantID
                    where i.EmpID='E0004'
                    order by i.AttachedDate desc 

select count(Status) from Interview where VacancyID = 'V0002' and Status = 'Scheduled'

select NumOfJob from Vacancy where VacancyID = 'V0002'

select v.VacancyID, v.VacancyTitle, a.ApplicantName, a.ApplicantID, i.AttachedDate, i.Status
from Vacancy v inner join Interview i
on v.VacancyID = i.VacancyID
inner join Applicant a
on a.ApplicantID = i.ApplicantID
where i.Status = 'Selected' and v.VacancyID = 'V0001'
order by i.AttachedDate desc